home *** CD-ROM | disk | FTP | other *** search
/ Personal Computer World 2005 October / PCWOCT05.iso / Software / FromTheMag / XAMPP 1.4.14 / xampp-win32-1.4.14-installer.exe / xampp / mysql / scripts / mysql_explain_log < prev    next >
Text File  |  2005-04-01  |  10KB  |  393 lines
  1. #!/usr/bin/perl -w
  2. use strict;
  3. use DBI;
  4.  
  5. use Getopt::Long;
  6. $Getopt::Long::ignorecase=0;
  7.  
  8. print "explain_log    provided by http://www.mobile.de\n";
  9. print "===========    ================================\n";
  10.  
  11. my $Param={};
  12.  
  13. $Param->{host}='';
  14. $Param->{user}='';
  15. $Param->{password}='';
  16. $Param->{PrintError}=0;
  17. $Param->{socket}='';
  18.  
  19. if (!GetOptions ('date|d:i' => \$Param->{ViewDate},
  20.          'host|h:s' => \$Param->{host},
  21.          'user|u:s' => \$Param->{user},
  22.          'password|p:s' => \$Param->{password},
  23.          'printerror|e:s' => \$Param->{PrintError},
  24.          'socket|s:s' => \$Param->{socket},
  25.         )) {
  26.   ShowOptions();
  27. }
  28. else {
  29.   $Param->{UpdateCount} = 0;
  30.   $Param->{SelectCount} = 0;
  31.   $Param->{IdxUseCount} = 0;
  32.   $Param->{LineCount} = 0;
  33.  
  34.   $Param->{Init} = 0;
  35.   $Param->{Field} = 0;
  36.   $Param->{Refresh} = 0;
  37.   $Param->{QueryCount} = 0;
  38.   $Param->{Statistics} =0;
  39.  
  40.   $Param->{Query} = undef;
  41.   $Param->{ALL} = undef ;
  42.   $Param->{Comment} = undef ;
  43.  
  44.   @{$Param->{Rows}} = (qw|possible_keys key type|);
  45.  
  46.   if ($Param->{ViewDate}) {
  47.     $Param->{View} = 0;
  48.   }
  49.   else {
  50.     $Param->{View} = 1;
  51.   }
  52.  
  53.   #print "Date=$Param->{ViewDate}, host=$Param->{host}, user=$Param->{user}, password=$Param->{password}\n";
  54.  
  55.   $Param->{dbh}=DBI->connect("DBI:mysql:host=$Param->{host}".($Param->{socket}?";mysql_socket=$Param->{socket}":""),$Param->{user},$Param->{password},{PrintError=>0});
  56.   if (DBI::err()) {
  57.     print "Error: " . DBI::errstr() . "\n";
  58.   }
  59.   else {
  60.     $Param->{Start} = time;
  61.     while(<STDIN>) {
  62.       $Param->{LineCount} ++ ;
  63.  
  64.       if ($Param->{ViewDate} ) {
  65.     if (m/^(\d{6})\s+\d{1,2}:\d\d:\d\d\s.*$/) { # get date
  66.       #print "# $1 #\n";
  67.       if ($1 == $Param->{ViewDate}) {
  68.         $Param->{View} = 1;
  69.       }
  70.       else {
  71.         $Param->{View} = 0;
  72.       }
  73.     }
  74.       }
  75.       if ($Param->{View} ) {
  76.     #print "->>>$_";
  77.  
  78.     if (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
  79.       #print "C-$1--$2--$3------\n";
  80.       RunQuery($Param);
  81.       if (defined $3) {
  82.         $Param->{CID}->{$2} = $3 ;
  83.         #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
  84.       }
  85.     }
  86.  
  87.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+$/i) { # get connection ID($2) and database($3)
  88.       #print "\n <<<<<<<<<<<<<<<<<<----------------------------<<<<<<<<<<<<<<<< \n";
  89.       #print "Connect \n";
  90.       RunQuery($Param);
  91.     }
  92.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Change user .*\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
  93.       #print "C-$1--$2--$3------\n";
  94.       RunQuery($Param);
  95.       if (defined $3) {
  96.         $Param->{CID}->{$2} = $3 ;
  97.         #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
  98.       }
  99.     }
  100.  
  101.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Quit\s+$/i) { # remove connection ID($2) and querystring
  102.       #print "Q-$1--$2--------\n";
  103.       RunQuery($Param);
  104.       delete $Param->{CID}->{$2} ;
  105.     }
  106.  
  107.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(select.+)$/i) { # get connection ID($2) and querystring
  108.       #print "S1-$1--$2--$3------\n";
  109.       RunQuery($Param);
  110.       unless ($Param->{CID}->{$2}) {
  111.         #print "Error: No Database for Handle: $2 found\n";
  112.       }
  113.       else {
  114.         $Param->{DB}=$Param->{CID}->{$2};
  115.  
  116.         my $s = "$3";
  117.         $s =~ s/from\s/from $Param->{DB}./i;
  118.         $Param->{Query}="EXPLAIN $s";
  119.  
  120.         #$s =~ m/from\s+(\w+[.]\w+)/i;
  121.         #$Param->{tab} =$1;
  122.         #print "-- $Param->{tab} -- $s --\n";
  123.       }
  124.     }
  125.  
  126.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(update.+)$/i) { # get connection ID($2) and querystring
  127.       #print "S2--$1--$2--$3------\n";
  128.       RunQuery($Param);
  129.       unless ($Param->{CID}->{$2}) {
  130.         #print "Error: No Database for Handle: $2 found\n";
  131.       }
  132.       else {
  133.         $Param->{DB}=$Param->{CID}->{$2};
  134.  
  135.         my $ud = $3;
  136.         $ud =~ m/^update\s+(\w+).+(where.+)$/i;
  137.         $Param->{Query} ="EXPLAIN SELECT * FROM $1 $2";
  138.         $Param->{Query} =~ s/from\s/from $Param->{DB}./i;
  139.  
  140.         #$Param->{Query} =~ m/from\s+(\w+[.]\w+)/i;
  141.         #$Param->{tab} =$1;
  142.       }
  143.     }
  144.  
  145.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Statistics\s+(.*)$/i) { # get connection ID($2) and info?
  146.       $Param->{Statistics} ++;
  147.       #print "Statistics--$1--$2--$3------\n";
  148.       RunQuery($Param);
  149.     }
  150.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(.+)$/i) { # get connection ID($2)
  151.       $Param->{QueryCount} ++;
  152.       #print "Query-NULL $3\n";
  153.       RunQuery($Param);
  154.     }
  155.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Refresh\s+(.+)$/i) { # get connection ID($2)
  156.       $Param->{Refresh} ++;
  157.       #print "Refresh\n";
  158.       RunQuery($Param);
  159.     }
  160.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Init\s+(.+)$/i) { # get connection ID($2)
  161.       $Param->{Init} ++;
  162.       #print "Init $3\n";
  163.       RunQuery($Param);
  164.     }
  165.     elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Field\s+(.+)$/i) { # get connection ID($2)
  166.       $Param->{Field} ++;
  167.       #print "Field $3\n";
  168.       RunQuery($Param);
  169.     }
  170.  
  171.     elsif (m/^\s+(.+)$/ ) { # command could be some lines ...
  172.       #print "multi-lined ($1)\n";
  173.       my ($A)=$1;
  174.        chomp $A;
  175.       $Param->{Query} .= " $1";
  176.       #print "multi-lined ($1)<<$Param->{Query}>>\n";
  177.     }
  178.  
  179.  
  180.       }
  181.  
  182.     }
  183.  
  184.     $Param->{dbh}->disconnect();
  185.  
  186.     if (1 == 0) {
  187.       print "\nunclosed handles----------------------------------------\n";
  188.       my $count=0;
  189.       foreach (sort keys %{$Param->{CID}}) {
  190.     print "$count | $_ : $Param->{CID}->{$_} \n";
  191.     $count ++;
  192.       }
  193.     }
  194.  
  195.     print "\nIndex usage ------------------------------------\n";
  196.     foreach my $t (sort keys %{$Param->{Data}}) {
  197.       print "\nTable\t$t: ---\n";
  198.       foreach my $k (sort keys %{$Param->{Data}->{$t}}) {
  199.     print " count\t$k:\n";
  200.     my %h = %{$Param->{Data}->{$t}->{$k}};
  201.       foreach (sort {$h{$a} <=> $h{$b}} keys %h) {
  202.       print "  $Param->{Data}->{$t}->{$k}->{$_}\t$_\n";
  203.     }
  204.       }
  205.     }
  206.  
  207.     $Param->{AllCount}=0;
  208.     print "\nQueries causing table scans -------------------\n\n";
  209.     foreach (@{$Param->{ALL}}) {
  210.       $Param->{AllCount} ++;
  211.       print "$_\n";
  212.     }
  213.     print "Sum: $Param->{AllCount} table scans\n";
  214.  
  215.     print "\nSummary ---------------------------------------\n\n";
  216.     print "Select: \t$Param->{SelectCount} queries\n";
  217.     print "Update: \t$Param->{UpdateCount} queries\n";
  218.     print "\n";
  219.  
  220.     print "Init:   \t$Param->{Init} times\n";
  221.     print "Field:  \t$Param->{Field} times\n";
  222.     print "Refresh: \t$Param->{Refresh} times\n";
  223.     print "Query:  \t$Param->{QueryCount} times\n";
  224.     print "Statistics:\t$Param->{Statistics} times\n";
  225.     print "\n";
  226.  
  227.     print "Logfile: \t$Param->{LineCount} lines\n";
  228.     print "Started:  \t".localtime($Param->{Start})."\n";
  229.     print "Finished:   \t".localtime(time)."\n";
  230.  
  231.   }
  232. }
  233.  
  234.  
  235. ###########################################################################
  236. #
  237. #
  238. #
  239. sub RunQuery {
  240.   my $Param = shift ;
  241.  
  242.   if (defined $Param->{Query}) {
  243.     if (defined $Param->{DB} ) {
  244.  
  245.       $Param->{Query} =~ m/from\s+(\w+[.]\w+|\w+)/i;
  246.       $Param->{tab} =$1;
  247.       #print "||$Param->{tab} -- $Param->{Query}\n";
  248.  
  249.       my $sth=$Param->{dbh}->prepare("USE $Param->{DB}");
  250.       if (DBI::err()) {
  251.     if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  252.       }
  253.       else {
  254.     $sth->execute();
  255.     if (DBI::err()) {
  256.       if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  257.     }
  258.     else {
  259.       $sth->finish();
  260.  
  261.       $sth=$Param->{dbh}->prepare($Param->{Query});
  262.       if (DBI::err()) {
  263.         if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  264.       }
  265.       else {
  266.         #print "$Param->{Query}\n";
  267.         $sth->execute();
  268.         if (DBI::err()) {
  269.           if ($Param->{PrintError}) {print "[$Param->{LineCount}]<<$Param->{Query}>>\n";}
  270.           if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
  271.         }
  272.         else {
  273.           my $row = undef;
  274.           while ($row = $sth->fetchrow_hashref()) {
  275.         $Param->{SelectCount} ++;
  276.  
  277.         if (defined $row->{Comment}) {
  278.           push (@{$Param->{Comment}}, "$row->{Comment}; $_; $Param->{DB}; $Param->{Query}");
  279.         }
  280.         foreach (@{$Param->{Rows}}) {
  281.           if (defined $row->{$_}) {
  282.             #if (($_ eq 'type' ) and ($row->{$_} eq 'ALL')) {
  283.             if ($row->{type} eq 'ALL') {
  284.               push (@{$Param->{ALL}}, "$Param->{Query}");
  285.               #print ">> $row->{$_} $_ $Param->{DB} $Param->{Query}\n";
  286.             }
  287.             $Param->{IdxUseCount} ++;
  288.             $Param->{Data}->{$Param->{tab}}->{$_}->{$row->{$_}} ++;
  289.           }
  290.         }
  291.           }
  292.         }
  293.       }
  294.     }
  295.       }
  296.       $sth->finish();
  297.     }
  298.     $Param->{Query} = undef ;
  299.   }
  300. }
  301.  
  302. ###########################################################################
  303. #
  304. #
  305. #
  306. sub ShowOptions {
  307.   print <<EOF;
  308. Usage: $0 [OPTIONS] < LOGFILE
  309.  
  310. --date=YYMMDD       select only entrys of date
  311. -d=YYMMDD
  312. --host=HOSTNAME     db-host to ask
  313. -h=HOSTNAME
  314. --user=USERNAME     db-user
  315. -u=USERNAME
  316. --password=PASSWORD password of db-user
  317. -p=PASSWORD
  318. --socket=SOCKET     mysqld socket file to connect
  319. -s=SOCKET
  320.  
  321. Read logfile from STDIN an try to EXPLAIN all SELECT statements. All UPDATE statements are rewritten to an EXPLAIN SELECT statement. The results of the EXPLAIN statement are collected and counted. All results with type=ALL are collected in an separete list. Results are printed to STDOUT.
  322.  
  323. EOF
  324. }
  325.  
  326. 1;
  327.  
  328. __END__
  329.  
  330. =pod
  331.  
  332. =head1 NAME
  333.  
  334. explain_log.pl
  335.  
  336. Feed a mysqld general logfile (created with mysqld --log) back into mysql
  337. and collect statistics about index usage with EXPLAIN.
  338.  
  339. =head1 DISCUSSION
  340.  
  341. To optimize your indices, you have to know which ones are actually
  342. used and what kind of queries are causing table scans. Especially
  343. if you are generating your queries dynamically and you have a huge
  344. amount of queries going on, this isn't easy.
  345.  
  346. Use this tool to take a look at the effects of your real life queries.
  347. Then add indices to avoid table scans and remove those which aren't used.
  348.  
  349. =head1 USAGE
  350.  
  351. explain_log.pl [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] [--socket=/path/to/socket] < logfile
  352.  
  353. --date=YYMMDD       select only entrys of date
  354.  
  355. -d=YYMMDD
  356.  
  357. --host=HOSTNAME     db-host to ask
  358.  
  359. -h=HOSTNAME
  360.  
  361. --user=USERNAME     db-user
  362.  
  363. -u=USERNAME
  364.  
  365. --password=PASSWORD password of db-user
  366.  
  367. -p=PASSWORD
  368.  
  369. --socket=SOCKET     change path to the socket
  370.  
  371. -s=SOCKET
  372.  
  373. =head1 EXAMPLE
  374.  
  375. explain_log.pl --host=localhost --user=foo --password=bar < /var/lib/mysql/mobile.log
  376.  
  377. =head1 AUTHORS
  378.  
  379.   Stefan Nitz
  380.   Jan Willamowius <jan@mobile.de>, http://www.mobile.de
  381.   Dennis Haney <davh@davh.dk> (Added socket support)
  382.  
  383. =head1 RECRUITING
  384.  
  385. If you are looking for a MySQL or Perl job, take a look at http://www.mobile.de
  386. and send me an email with your resume (you must be speaking German!).
  387.  
  388. =head1 SEE ALSO
  389.  
  390. mysql documentation
  391.  
  392. =cut
  393.